﻿using DbUp.Helpers;
using DbUp;
using MySql.Data.MySqlClient;
using System;
using System.IO;
using System.Threading.Tasks;
using System.Windows;
using System.Text;
using System.Diagnostics; 

namespace ServiceControllerApp.SqlImporter
{

    public class ImportResult
    {
        public bool Success
        {
            get; set;
        }
        public string ErrorMessage
        {
            get; set;
        }
    }


    public class SqlBackupImporter
    {
        private string sqlFilePath;
        private string databaseName;
        private string username;
        private string password;
        private string serverHost;
        private string port;

        public SqlBackupImporter(string sqlFilePath, string databaseName, string username, string password, string serverHost, string port)
        {
            this.sqlFilePath = sqlFilePath;
            this.databaseName = databaseName;
            this.username = username;
            this.password = password;
            this.serverHost = serverHost;
            this.port = port;
        }
        public async Task<ImportResult> ImportSqlBackup()
        {
            ImportResult result = new ImportResult();

            // 清理 SQL 文件，去除注释等无关内容
            // string cleanSqlFilePath = CleanSqlFile(sqlFilePath);

            string connectionString = $"Server={serverHost};Port={port};User Id={username};Password={password};CharSet=utf8mb4;";
            try
            {
                // 使用 Task.Run 来确保 SQL 导入在后台线程执行
                await Task.Run(async () =>
                {
                    try
                    {
                        using (var connection = new MySqlConnection(connectionString))
                        {
                            await connection.OpenAsync();

                            // 检查数据库是否存在
                            var command = new MySqlCommand($"SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '{databaseName}';", connection);
                            var existDb = await command.ExecuteScalarAsync();

                            if (existDb == null)
                            {
                                // 如果数据库不存在，则创建
                                command = new MySqlCommand($"CREATE DATABASE `{databaseName}` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;", connection);
                                await command.ExecuteNonQueryAsync();
                            }
                           
                            //var processInfo = new ProcessStartInfo
                            //{
                            //    FileName = "mysql",
                            //    Arguments = $"-h {serverHost} -P {port} -u {username} -p{password} {databaseName} < \"{sqlFilePath}\"",
                            //    RedirectStandardError = true,
                            //    RedirectStandardOutput = true,
                            //    UseShellExecute = false,
                            //    CreateNoWindow = true
                            //};

                            //using (var process = Process.Start(processInfo))
                            //{
                            //    string output = await process.StandardOutput.ReadToEndAsync();
                            //    string error = await process.StandardError.ReadToEndAsync();
                            //    process.WaitForExit();
                            //    Console.WriteLine(output);
                            //    if (process.ExitCode != 0)
                            //    {
                            //        result.Success = false;
                            //        result.ErrorMessage = error;
                            //        Console.WriteLine(error);
                            //    }
                            //    else
                            //    {
                            //        result.Success = true;
                            //    }
                            //}

                            // 配置日志文件路径
                            //string appDirectory = System.IO.Path.GetDirectoryName(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName);
                            //string logFilePath = Path.Combine(appDirectory, "DbUpLog.txt");

                            // 配置 DbUp 升级器
                            //connectionString = $"Server={serverHost};Port={port};Database={databaseName};User Id={username};Password={password};CharSet=utf8mb4;";
                            //var upgrader = DeployChanges.To
                            //    .MySqlDatabase(connectionString)
                            //    //.WithScriptsFromFileSystem(sqlFilePath)
                            //    .WithScriptsFromFileSystem(new FileInfo(sqlFilePath).Directory.FullName, sqlFilePath => sqlFilePath.Contains("bak_db"), Encoding.UTF8)
                            //    .LogTo(new DbUpLogger())  // 使用自定义日志记录器
                            //    //.WithTransaction()  // 使用单一事务                 //.LogToConsole()
                            //    .JournalTo(new NullJournal()) // 不使用持久化的日志
                            //    .Build();

                            //// 执行导入
                            //var upgradeResult = upgrader.PerformUpgrade();

                            //if (upgradeResult.Successful)
                            //{
                            //    result.ErrorMessage = "SQL 导入成功。";
                            //    result.Success = true;
                            //}
                            //else
                            //{
                            //    Console.ForegroundColor = ConsoleColor.Red;
                            //    Console.WriteLine("xx " + upgradeResult.Error.Message);
                            //    Console.WriteLine("aa " + upgradeResult.Error.StackTrace);
                            //    Console.ResetColor();
                            //    result.ErrorMessage = $"SQL 导入失败: {upgradeResult.Error.Message}";
                            //    result.Success = false;
                            //}
                        }
                        connectionString = $"Server={serverHost};Port={port};Database={databaseName};User Id={username};Password={password};CharSet=utf8mb4;convertzerodatetime=true;";
                        //using (MySqlConnection conn = new MySqlConnection(connectionString))
                        //{
                        //    using (MySqlCommand cmd = new MySqlCommand())
                        //    {
                        //        cmd.Connection = conn;
                        //        conn.Open();
                        //        MySqlBackup.Restore restore = new MySqlBackup.Restore(cmd);
                        //        restore.ImportFromFile(sqlFilePath);
                        //        restore.Execute();
                        //        Console.WriteLine("SQL文件导入成功");
                        //    }

                        //}
                        using (MySqlConnection conn = new MySqlConnection(connectionString))
                        {
                            using (MySqlCommand cmd = new MySqlCommand())
                            {
                                using (MySqlBackup mb = new MySqlBackup(cmd))
                                {
                                    cmd.Connection = conn;
                                    conn.Open();
                                    mb.ImportFromFile(sqlFilePath);
                                    conn.Close();
                                    Console.WriteLine("SQL文件导入成功");
                                }
                            }
                        }
                        result.ErrorMessage = $"SQL文件导入成功";
                        result.Success = true;
                    }
                    catch (MySqlException ex)
                    {
                        result.ErrorMessage = $"MySQL数据库操作出错: {ex.Message}";
                        result.Success = false;
                    }
                    catch (IOException ex)
                    {
                        result.ErrorMessage = $"文件读取操作出错: {ex.Message}";
                        result.Success = false;
                    }
                    catch (Exception ex)
                    {
                        result.ErrorMessage = $"未知错误: {ex.Message}";
                        result.Success = false;
                    }
                });
            }
            catch (Exception ex)
            {
                result.ErrorMessage = $"在启动任务时发生错误: {ex.Message}";
                result.Success = false;
            }

            return result;
        }
        //public async Task<ImportResult> ImportSqlBackup()
        //{
        //    ImportResult result = new ImportResult();

        //    // 清理 SQL 文件，去除注释等无关内容
        //    string cleanSqlFilePath = CleanSqlFile(this.sqlFilePath);

        //    string connectionString = $"Server={serverHost};Port={port};User Id={username};Password={password};CharSet=utf8mb4;";
        //    try
        //    {
        //        using (var connection = new MySqlConnection(connectionString))
        //        {
        //            await connection.OpenAsync();

        //            // 检查数据库是否存在
        //            var command = new MySqlCommand($"SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '{databaseName}';", connection);
        //            var existDb = await command.ExecuteScalarAsync();

        //            if (existDb == null)
        //            {
        //                // 如果数据库不存在，则创建
        //                command = new MySqlCommand($"CREATE DATABASE `{databaseName}` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;", connection);
        //                await command.ExecuteNonQueryAsync();
        //            }

        //            //// 使用目标数据库
        //            //command = new MySqlCommand($"USE `{databaseName}`;", connection);
        //            //await command.ExecuteNonQueryAsync();

        //            //// 事务对象，用于控制数据导入的事务
        //            //MySqlTransaction transaction = null;

        //            //using (var reader = new StreamReader(cleanSqlFilePath))
        //            //{
        //            //    int commandCount = 0; // 创建计数器
        //            //    string line;
        //            //    while ((line = reader.ReadLine()) != null)
        //            //    {
        //            //        line = line.Trim();
        //            //        if (!string.IsNullOrEmpty(line))
        //            //        {
        //            //            if (transaction == null)
        //            //            {
        //            //                transaction = connection.BeginTransaction();
        //            //            }

        //            //            using (var cmd = new MySqlCommand(line, connection, transaction))
        //            //            {
        //            //                try
        //            //                {
        //            //                    await cmd.ExecuteNonQueryAsync();
        //            //                    commandCount++; // 增加命令计数器

        //            //                    // 每执行100条命令提交一次事务
        //            //                    if (commandCount % 100 == 0)
        //            //                    {
        //            //                        transaction.Commit(); // 提交当前事务
        //            //                        transaction = connection.BeginTransaction(); // 开始一个新的事务
        //            //                    }
        //            //                }
        //            //                catch (MySqlException ex)
        //            //                {
        //            //                    if (transaction != null)
        //            //                    {
        //            //                        try
        //            //                        {
        //            //                            transaction.Rollback(); // 回滚事务
        //            //                        }
        //            //                        catch (MySqlException rollbackEx)
        //            //                        {
        //            //                            Console.WriteLine($"事务回滚出错: {rollbackEx.Message}");
        //            //                        }
        //            //                    }
        //            //                    result.ErrorMessage = ex.Message;
        //            //                    result.Success = false;
        //            //                    return result;
        //            //                }
        //            //            }
        //            //        }
        //            //    }

        //            //    // 提交剩余的事务
        //            //    if (transaction != null)
        //            //    {
        //            //        transaction.Commit();
        //            //    }

        //            //}

        //            result.Success = true;
        //        }
        //    }
        //    catch (MySqlException ex)
        //    {
        //        result.ErrorMessage = $"MySQL数据库操作出错: {ex.Message}";
        //        result.Success = false;
        //    }
        //    catch (IOException ex)
        //    {
        //        result.ErrorMessage = $"文件读取操作出错: {ex.Message}";
        //        result.Success = false;
        //    }
        //    catch (Exception ex)
        //    {
        //        result.ErrorMessage = $"未知错误: {ex.Message}";
        //        result.Success = false;
        //    }

        //    return result;
        //}

        // 用于清理 SQL 文件内容的辅助方法
        //private string CleanSqlFile(string filePath)
        //{
        //    var cleanedLines = new List<string>();
        //    var lines = File.ReadAllLines(filePath);
        //    bool insideCommentBlock = false;

        //    foreach (var line in lines)
        //    {
        //        // 检查是否进入多行注释块
        //        if (line.Trim().StartsWith("/*"))
        //        {
        //            insideCommentBlock = true;
        //        }

        //        // 如果在注释块内，跳过当前行
        //        if (insideCommentBlock)
        //        {
        //            // 检查是否结束多行注释块
        //            if (line.Contains("*/"))
        //            {
        //                insideCommentBlock = false;
        //            }
        //            continue;
        //        }

        //        // 处理单行注释
        //        if (line.Trim().StartsWith("--"))
        //        {
        //            continue;
        //        }

        //        // 将非注释行添加到结果列表中
        //        cleanedLines.Add(line);
        //    }

        //    // 将清理后的内容保存到一个新文件
        //    string cleanedFilePath = Path.Combine(Path.GetDirectoryName(filePath), "cleaned_" + Path.GetFileName(filePath));
        //    File.WriteAllLines(cleanedFilePath, cleanedLines);
        //    return cleanedFilePath;
        //}

    }
}
